--roznica miedzy instert a update
--


if exists(select 1 from master.dbo.sysdatabases where name = 'bazaIO') drop database bazaIO
GO
CREATE DATABASE bazaIO
GO

USE bazaIO;
GO

---------------------------------- PRODUCT
Create table Product (
idProduct int IDENTITY(1,1) PRIMARY KEY,
name varchar(20) not null,
price double precision,
category varchar(20) not null,
descriptionn varchar(20) not null,
);
GO

Create table Pictures (
idProduct int,
link varchar(100),
CONSTRAINT FKPictureProduct FOREIGN KEY (idProduct) REFERENCES Product (idProduct),
);
GO

Create table Details (
idProduct int ,
keyy varchar(20) not null,
value  varchar(100) not null,
CONSTRAINT FKDetailsProduct FOREIGN KEY (idProduct) REFERENCES Product (idProduct),
);
GO
------------------------------------/PRODUCT
--
--
------------------------------------MAGAZINE
Create table Magazine (
idProduct int ,
quantity int,
CONSTRAINT FKMagazineProduct FOREIGN KEY (idProduct) REFERENCES Product (idProduct),
);
GO
------------------------------------/MAGAZINE
--
--
------------------------------------USERR
Create table Userr (
idUserr int IDENTITY(1,1) PRIMARY KEY,
loginn varchar(20) not null,
passwordd varchar(20) not null,
userrName varchar(20) not null,
firstName varchar(20) not null,
lastName varchar(20) not null,
licenseNumber varchar(20) not null,
userrTYPE varchar(20) not null,
nip varchar(20) not null,
regon varchar(20) not null,
CONSTRAINT check_userrtype check ( userrType in ('SALESMAN','CUSTOMER','MANAGER'))
);
GO

Create table Addres (
idUserr int,
city varchar(20) not null,
street varchar(20) not null,
houseNo varchar(20) not null,
flatNO varchar(20) not null,
postalCode varchar(20) not null
CONSTRAINT FKAddresUserr FOREIGN KEY (idUserr) REFERENCES Userr (idUserr)
);
GO

Create table UserrPermission (
idUserr int,
permission varchar(20) not null,
CONSTRAINT check_permission check ( permission in ('EDIT_ACCOUNTS','EDIT_PRODUCTS','MAKE_ORDERS','CANCLE_ORDERS')),
CONSTRAINT FKUserrPermissionUserr FOREIGN KEY (idUserr) REFERENCES Userr (idUserr)
);
GO
------------------------------------/USERR
--
--
------------------------------------INVOICE
Create table Invoice (
number int IDENTITY(1,1) PRIMARY KEY,
idUserr int,
invoiceType varchar(20) not null,
paymentMethod varchar(20) not null,
price double precision,
creationDate date,
sellDate date,
deadlineDate date
CONSTRAINT FKInvoiceUserr FOREIGN KEY (idUserr) REFERENCES Userr (idUserr),
CONSTRAINT check_paymentMethod check ( paymentMethod in ('CASH','TRANSFER')),
CONSTRAINT check_invoiceType check ( invoiceType in ('VAT','WZ'))
);
GO

Create table Products (
number int,
idProduct int,
amount int,
CONSTRAINT FKProductsInvoice FOREIGN KEY (number) REFERENCES Invoice (number),
CONSTRAINT FKProductsProduct FOREIGN KEY (idProduct) REFERENCES Product (idProduct)
);
------------------------------------/INVOICE
--
--
------------------------------------ORDERR
Create table Orderr (
numberOrderr int IDENTITY(1,1) PRIMARY KEY,
number int,
CONSTRAINT FKOrderrInvoice FOREIGN KEY (number) REFERENCES Invoice (number),
);



Create table Properties (
numberOrderr int,
options varchar(20) not null,
advancedOptions varchar(20) not null,
CONSTRAINT FKPropertiesOrderr FOREIGN KEY (numberOrderr) REFERENCES Orderr (numberOrderr),
);
------------------------------------/ORDERR
--
--
------------------------------------INSERT
INSERT INTO Product (name,price,category,descriptionn) VALUES ('szafkaa','5.1','meble','opis');
INSERT INTO Product (name,price,category,descriptionn) VALUES ('okno','10','meble','bb');
INSERT INTO Product (name,price,category,descriptionn) VALUES ('drzwi','20','meble','aa');
--
INSERT INTO Pictures (idProduct,link) VALUES (3,'link2');
INSERT INTO Pictures (idProduct,link) VALUES (3,'link1');
--
INSERT INTO Details (idProduct,keyy,value) VALUES ('1','klucz1','wartosc1');
INSERT INTO Details (idProduct,keyy,value) VALUES ('3','klucz2','wartosc2');
INSERT INTO Details (idProduct,keyy,value) VALUES ('1','klucz3','wartosc2');
--
INSERT INTO Magazine (idProduct,quantity) VALUES (1,10);
--
INSERT INTO Userr (loginn,passwordd,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon) VALUES ('?','?','?','?','?','?','?','?','?');
INSERT INTO Userr (loginn,passwordd,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon) VALUES ('loign1','pass1','usernam1','stname1','LastNam1','ln1','SALESMAN','132','654');
INSERT INTO Userr (loginn,passwordd,userrName,firstName,lastName,licenseNumber,userrTYPE,nip,regon) VALUES ('log2','ps1','USER','janek','ziomek','licNR 123','SALESMAN','555','666');
--
INSERT INTO Addres (idUserr,city,street,houseNo,flatNO,postalCode) VALUES ('?','?','?','?','?','?');
INSERT INTO Addres (idUserr,city,street,houseNo,flatNO,postalCode) VALUES (2,'city1','street1','homenr1','flatno1','postC1');
--
INSERT INTO UserrPermission (idUserr,permission) VALUES ('?','?');
INSERT INTO UserrPermission (idUserr,permission) VALUES (2,'EDIT_ACCOUNTS');
--
INSERT INTO Invoice (idUserr,invoiceType,paymentMethod,price,creationDate,sellDate,deadlineDate) VALUES ('?','?','?','?','?','?','?');
INSERT INTO Invoice (idUserr,invoiceType,paymentMethod,price,creationDate,sellDate,deadlineDate) VALUES (2,'VAT','CASH',10.1,null,null,null);
INSERT INTO Invoice (idUserr,invoiceType,paymentMethod,price) VALUES (2,'CUSTOMER','CASH',10.1);
INSERT INTO Invoice (idUserr,invoiceType,paymentMethod,price) VALUES (3,'CUSTOMER','CASH',15.1);
INSERT INTO Invoice (idUserr,invoiceType,paymentMethod,price,creationDate,sellDate,deadlineDate) VALUES (3,'CUSTOMER','CASH',10.1,'2013-12-28',null,null);
--
INSERT INTO Products (number,idProduct,amount) VALUES ('?','?','?');
INSERT INTO Products (number,idProduct,amount) VALUES (4,1,7);
INSERT INTO Products (number,idProduct,amount) VALUES (5,3,2)
--
INSERT INTO Orderr (number) VALUES ('?');
INSERT INTO Orderr (number) VALUES (6);
--
INSERT INTO Properties (numberOrderr,options,advancedOptions) VALUES ('?','?','?');
INSERT INTO Properties (numberOrderr,options,advancedOptions) VALUES (1,'option1','adoption2');
------------------------------------/INSERT
--
--
------------------------------------UPDTAE
UPDATE Userr set loginn=?, userrName=?, firstName=?, lastName=?, licenseNumber=?, userrTYPE=?, nip=?, regon=?, passwordd=? where idUserr=1;
--
UPDATE Product set name=?, descriptionn=?,price=?,category=?  where idProduct=?;
--
UPDATE Userr set loginn=?, userrName=?, firstName=?, lastName=?, licenseNumber=? userrTYPE=?, nip=?, regon=? where idUserr=?;
--
UPDATE Addres set city=?, street=?, houseNo=?, flatNO=?, postalCode=? where idUserr=?;
--
UPDATE Magazine set quantity=?  where idProduct=3;
--
UPDATE Invoice set idUserr=?, invoiceType=?, paymentMethod=?, price=?, creationDate='2013-12-26', sellDate=?, deadlineDate=? where number=?; 
UPDATE Invoice set creationDate='2013-12-26' where number=5; 
--
UPDATE Orderr set number=? where numberOrderr=?;
-------------------------------------UPDATE W INNY SPOSOD dla poszczegolnych tabel
DELETE FROM Magazine WHERE idProduct=? ;
INSERT INTO Magazine (idProduct,quantity) VALUES (?,?);
--
DELETE FROM Details WHERE idProduct= ?;
INSERT INTO Details (idProduct,keyy,value) VALUES (?,?,?);
--
DELETE FROM UserrPermission WHERE idUserr= ?;
INSERT INTO UserrPermission (idUserr,permission) VALUES ('?','?');
--
DELETE FROM Products WHERE number= ?;
INSERT INTO Products (number,idProduct,amount) VALUES (?,?,?);
--
DELETE FROM Properties WHERE numberOrderr=?
INSERT INTO Properties (numberOrderr,options,advancedOptions) VALUES (?,?,?);
------------------------------------/UPDATE
--
--
-------------------------------------DROP
drop table Product
drop table Pictures
drop table Details
drop table Magazine
drop table Addres
drop table Userr
drop table UserrPermission
drop table Products
drop table Invoice
drop table Orderr
drop table Properties 
-------------------------------------/DROP
--
--
-------------------------------------SELECT
select * from Product;
select * from Pictures;
select * from Details;
select * from Addres;
select * from Userr;
select * from UserrPermission;
select * from Invoice;
select * from Products;
select * from Orderr;
select * from Properties;
-------------------------------------SELECT po ID
select * from Product where idProduct= ?;
select * from Pictures where idProduct= ?;
select * from Details where idProduct= ?;
select * from Magazine where idProduct= ?;
select * from Addres where idUserr= ?;
select * from Userr where idUserr= ?;
select * from UserrPermission where idUserr= ?;
select * from Invoice where number= ?;
select * from Products where number= ?;
select * from Orderr where numberOrderr=?;
select * from Properties where numberOrderr=?;
-------------------------------------/SELECT

--getUserOrder
select * from Orderr 
where Orderr.number=ANY(select Invoice.number from Invoice 
where  idUserr=(select idUserr from Userr where idUserr=3))

--getUserOrderHisotry
select * from Orderr 
where Orderr.number=ANY(select Invoice.number from Invoice 
where Invoice.creationDate > '2013-12-26' and Invoice.creationDate <'2013-12-30' and
idUserr=(select idUserr from Userr where idUserr=3))

-- GetUsersProduct
Select name from Product where Product.idProduct=
(select Products.idProduct from Products, Orderr where Products.number=Orderr.number and numberOrderr=
(select numberOrderr from Orderr where numberOrderr=2))

--getUserOrderProducts
Select Products.idProduct , amount from Products, Product 
where Products.idProduct=Product.idProduct and Products.number=(select number from Orderr where numberOrderr=2)